﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.IO;
using CTEM.Controllers.Global;

using CTEM.Models;
using System.Diagnostics;
using CTEM.Controllers.Global;

namespace CTEM.Controllers.DAO
{
    /// <summary>
    /// 
    /// </summary>
    class CT_MaterialDAO
    {
        public CT_MaterialDAO()
        {
            Transaction = null;
        }

        /// <summary>
        /// 
        /// </summary>
        private StackFrame _currentStackFrame;

        /// <summary>
        /// 
        /// </summary>
        public SqlTransaction Transaction { get; set; }
        
        /// <summary>
        /// 
        /// </summary>
        private SqlConnection _con;

        /// <summary>
        /// 
        /// </summary>
        SqlConnection Connection
        {
            get
            {
                if (_con == null)
                    _con = new SqlConnection(DatabaseInfo.ConnectionString);
                if (_con.State == ConnectionState.Closed)
                {
                    _con.Open();
                }
                return _con;
            }
            set
            {
                _con = value;
            }
        }

        internal long CreateMaterial(CT_MaterialDTO material)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CT_CreateMaterial]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@MaterialName", 
                                                  "@Unit", 
                                                  "@LastPrice", 
                                                  "@IsActive", 
                                                  "@IsDelete",
                                                  "@CreatedBy", 
                                                  "@CreatedDate", };
                    object[] parametervalues = { material.MaterialName, 
                                                   material.Unit, 
                                                   material.LastPrice, 
                                                   material.IsActive, 
                                                   material.IsDelete,
                                                   material.CreatedBy, 
                                                   material.CreatedDate };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                            return Convert.ToInt64(reader[0]);
                    }
                    return 0;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }
        
        internal bool UpdateMaterial(CT_MaterialDTO material)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CT_UpdateMaterial]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@MaterialID", 
                                                  "@MaterialName", 
                                                  "@Unit", 
                                                  "@LastPrice", 
                                                  "@IsActive", 
                                                  "@IsDelete",
                                                  "@UpdatedBy", 
                                                  "@LastUpdated", };
                    object[] parametervalues = { material.MaterialID, 
                                                   material.MaterialName, 
                                                   material.Unit, 
                                                   material.LastPrice, 
                                                   material.IsActive, 
                                                   material.IsDelete,
                                                   material.UpdatedBy, 
                                                   material.LastUpdated };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    cmd.ExecuteReader();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        internal bool DeleteMaterial(long materialID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CT_DeleteMaterial]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    cmd.Parameters.Add(new SqlParameter("@MaterialID", materialID));
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        internal CT_MaterialDTO GetMaterialByID(long materialID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CT_GetMaterialByID]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@MaterialID", materialID));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            CT_MaterialDTO materialDTO = new CT_MaterialDTO
                            {
                                MaterialID = Convert.ToInt64(reader["MaterialID"]),
                                MaterialName = Convert.ToString(reader["MaterialName"]),
                                Unit = Convert.ToString(reader["Unit"]),
                                LastPrice = Convert.ToInt64(reader["LastPrice"]),
                                IsActive = Convert.ToBoolean(reader["IsActive"]),
                                IsDelete = Convert.ToBoolean(reader["IsDelete"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            return materialDTO;
                        }
                    }
                    return null;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        internal List<CT_MaterialDTO> GetAllMaterial(bool isactive)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CT_GetAllMaterial]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                if (isactive)
                {
                    cmd.Parameters.Add(new SqlParameter("@IsActive", isactive));
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@IsActive", DBNull.Value));
                }
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<CT_MaterialDTO> listmaterials = new List<CT_MaterialDTO>();
                        while (reader.Read())
                        {
                            CT_MaterialDTO materialDTO = new CT_MaterialDTO
                            {
                                MaterialID = Convert.ToInt64(reader["MaterialID"]),
                                MaterialName = Convert.ToString(reader["MaterialName"]),
                                Unit = Convert.ToString(reader["Unit"]),
                                LastPrice = Convert.ToInt64(reader["LastPrice"]),
                                IsActive = Convert.ToBoolean(reader["IsActive"]),
                                IsDelete = Convert.ToBoolean(reader["IsDelete"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            listmaterials.Add(materialDTO);
                        }
                        return listmaterials;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }
    }
}
